Você é funcionário dos correios da cidade de Boston e, como tal, supervisiona a entrega de correspondência em toda a cidade. Na maior parte, todos os pacotes enviados são eventualmente entregues. Exceto que, de vez em quando, um mistério cai no seu colo: um pacote perdido! Para cada cliente que chega até você com um relatório de pacote perdido, sua tarefa é determinar:

  1. O endereço atual (ou localização) da entrega
  2. O tipo de endereço (residencial, comercial, etc…)
  3. O conteúdo do pacote

Tudo o que você sabe é o que os próprios clientes lhe dirão. Para resolver cada mistério, você precisará usar o banco de dados do serviço de entrega de correio, packages.db BAIXAR, que contém dados sobre o trânsito de pacotes pela cidade. Usando apenas as informações do banco de dados, sua tarefa é ajudar cada cliente a encontrar o pacote perdido.

Schema

packages.db representa todas as entregas recentes de pacotes na cidade de Boston. Para fazer isso, packages.db representa as seguintes entidades:

Dentro de packages.db, você encontrará diversas tabelas. Clique nos menus abaixo para saber mais sobre o esquema de cada tabela individual.

tabela addresses (endereços)

A tabela addresses contém as seguintes colunas:

  • id: identifica o id do endereço
  • address: o endereço em si
  • type: tipo de endereço (residencial, comercial, etc…)
tabela drivers (motoristas)

A tabela drivers contém as seguintes colunas:

  • id: identifica o id do endereço
  • name: o nome do motorista
tabela packages (pacotes/encomendas)

A tabela drivers contém as seguintes colunas:

  • id: identifica o id da encomenda (pacote)
  • contents: o conteúdo da encomenda
  • from_address_id: endereço de onde o pacote foi enviado
  • to_address_id: o endereço que o pacote deve chegar. Não necessáriamente é onde ele finaliza!
tabela scans (logs)

A tabela scans contém as seguintes colunas:

  • id: identifica o id do log
  • driver_id: id do motorista que criou o log
  • package_id: endereço do pacote que foi transportado
  • address_id: id do endereço que foi feito o log
  • action: indica se o pacote foi coletado (“Picked”) ou entregue (“Drop”) no endereço
  • timestamp: indica o dia e hora que o log foi registrado

Questões para resolver

Agora que você sabe as informações do banco de dados, antes de resolver os problemas dos clientes convém determinar o diagrama ER para os dados.

1 Diagrama dos dados

Crie o diagrama ER para os dados e escreva o nome das colunas de cada tabela ao lado da mesma (retângulos no diagrama). Use algum software para criar o diagrama.

2 A carta perdida

Seu primeiro relato de um pacote desaparecido vem de Anneke. Anneke vai até o seu balcão e diz o seguinte:

Sr(a) atendente, meu nome é Anneke. Eu moro na 900 Somerville Avenue. Não faz muito tempo, enviei uma carta especial (Congratulatory letter). É para meu amigo Varsha. Ela está começando um novo capítulo de sua vida em 2 Finnegan Street, na parte alta da cidade. (Esse endereço, deixe-me dizer: foi um pouco complicado acertar na primeira vez.) A carta é uma nota de felicitações – um alegre abraço de papel meu para ela, para comemorar essa grande mudança dela. Você pode verificar se já chegou até ela?

Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.

3 A entrega “melindrosa”

Seu segundo relato de um pacote desaparecido vem de um sujeito misterioso de fora da cidade. Ele vai até o seu balcão e dizem o seguinte:

Bom dia para você, entregador da correspondência. Você deve se lembrar que não faz muito tempo vim da cidade de Fiftyville. Entreguei uma certa caixa em suas mãos confiáveis e pedi que mantivesse as coisas em segredo. Meu associado já esperava o pacote há algum tempo. E ainda assim, parece ter criado asas e voado para longe. Ha! Alguma chance de você ajudar a esclarecer esse mistério? Receio que não haja endereço “De”. É o tipo de pacote que poderia causar….problemas a algumas pessoas, se é que me entende!

Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.

4 O presente esquecido

Seu terceiro relato de desaparecimento de um pacote vem de um avô que mora na mesma rua do correio. Ele se aproxima do seu balcão e diz o seguinte:

Ah, com licença, escriturário. Eu tinha enviado um presente misterioso para minha neta maravilhosa, no endereço 728 de Maple Place. Isso foi há cerca de duas semanas. Agora o prazo do envio já passou de sete dias inteiros e ouvi dizer que ela ainda espera, com as mãos vazias e o coração cheio de expectativas. Estou um pouco preocupado em saber para onde foi meu pacote. Não consigo lembrar o que há dentro, mas sei que está cheio até a borda com meu amor por ela. Podemos rastreá-lo para que possa encher o dia dela de alegria? Enviei-o da minha casa, no enderço 109 da Tileston Street.

Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.

RESPOSTAS

Ver

1 Diagrama ER

2 A carta perdida

Primeiro encontrando os dados do endereço 900 Somerville Avenue:

  SELECT * FROM "addresses" WHERE "address" = '900 Somerville Avenue'
Ver resultado

Com o id (432) do endereço, podemos buscar as entregas que possuem esse id como inicio (from_address_id)

  SELECT * FROM "packages" WHERE "from_address_id" = 432;
Ver resultado

Agora vemos que o pacote Congratulatory letter tem id 384, podemos buscar nos logs (scans) os trâmites desse pacote:

  SELECT * FROM "scans" WHERE "package_id" = 384;
Ver resultado

Agora vemos que a última transação do pacote foi uma entrega para o endereço 854. Vejamos qual é este endereço:

  SELECT * FROM "addresses" WHERE "id" = 854;
Ver resultado

Se juntarmos tudo em uma query:

SELECT "address" FROM "addresses" WHERE "id" = (
  SELECT "address_id" FROM "scans" WHERE "package_id" = (
    SELECT "id" FROM "packages" WHERE  ("contents" = 'Congratulatory letter') AND ("from_address_id" = (
      SELECT "id" FROM "addresses" WHERE "address" = '900 Somerville Avenue'))
      ) ORDER BY "timestamp" DESC LIMIT 1
);
Ver resultado

3 A entrega “melindrosa”

Encontrando uma entrega que não possua remetente (from_address_id). Nessa query já conseguimos ver o conteúdo do pacote.

  SELECT * FROM "packages" WHERE "from_address_id" IS NULL;
Ver resultado

Verificando os scans que tem o id 5098:

  SELECT * FROM "scans" WHERE "package_id" = 5098;
Ver resultado

Coletando o id do endereço da ultima transação (348), e verificando os dados do mesmo:

  SELECT * FROM "addresses" WHERE "id" = 348;
Ver resultado

O endereço é uma estação de polícia!

Criando uma query única:

  SELECT * FROM "addresses" WHERE "id" = 
  (
     SELECT "address_id" FROM "scans" WHERE "package_id" = 
     (
        SELECT "id" FROM "packages" WHERE "from_address_id" IS NULL 
     ) ORDER BY "timestamp" DESC LIMIT 1
  );
Ver resultado

4 O presente esquecido

Encontrando o último scan que possua o pacote cujo endereço é 109 Tileston Street:

  SELECT * FROM "scans" WHERE "package_id" = 
   (
   SELECT "id" FROM "packages" WHERE "from_address_id" = 
      (
      SELECT "id" FROM "addresses" WHERE "address" = '109 Tileston Street'
      )
    ) ORDER BY "timestamp" DESC LIMIT 1;
Ver resultado